CREATE TRIGGER checkNumOfDrugs
ON Prescribes FOR INSERT,UPDATE AS

BEGIN
	DECLARE @date DATETIME
	DECLARE @patientId INT
	DECLARE @doctorId INT
    DECLARE @updatedCount INT

    SET @updatedCount = (SELECT COUNT(*) FROM INSERTED)

    IF (@updatedCount = 1)
    BEGIN
	    SET @date = (SELECT date FROM INSERTED)
	    SET @patientId = (SELECT patientId FROM INSERTED)
	    SET @doctorId = (SELECT doctorId FROM INSERTED)

	    /*for children(age <5), number of drugs less than or equal to 4*/
	    IF(	@patientId IN 
			(SELECT patientId FROM YoungPatients) 
			AND 
			(SELECT COUNT(*)
			 FROM Prescribes
			 WHERE	patientId = @patientId 
					AND doctorId = @doctorId 
					AND date = @date) > 3)
					
		BEGIN
			PRINT 'For patient age < 5, the number of drugs must be less than 4.' 
			ROLLBACK
		END

	    /*for non-children(age>=5) number of drugs per prescription is more than or equal to 10*/
	    IF(	@patientId NOT IN 
			(SELECT patientId FROM YoungPatients)
			AND	
			(SELECT COUNT(*)
			 FROM Prescribes
			 WHERE	patientId = @patientId 
					AND doctorId = @doctorId 
					AND date = @date) > 9) 
					
		BEGIN
			PRINT 'For patient age >=5, the number of drugs in a prescription must be less than 10.'			   
			ROLLBACK
		END
    END
END
